--课程设计代码

-- 创建数据库
create database xj_新冠疫苗接种管理系统							-- 创建名为xj_新冠疫苗接种管理系统的数据库
	on							
	(name=xj_新冠疫苗接种管理系统,										-- 逻辑文件名为xj_新冠疫苗接种管理系统
	filename='C:\data\xj_新冠疫苗接种管理系统.mdf',			-- 创建物理文件名，将文件存在数据库文件夹中
	size=5,  																											-- 初始大小为5
	filegrowth=1																										-- 文件增幅长度为1
	)																														-- 初始大小为2
log on
	(name=xj_新冠疫苗接种管理系统_log,								-- xj_新冠疫苗接种管理系统_log
	filename='C:\data\xj_新冠疫苗接种管理系统_log.ldf',		-- 与之前mdf文件存在相同文件夹中
	size=2,																												-- 初始大小为2
	maxsize=20,																										-- 文件最大长度为20
	filegrowth=10%																								-- 文件增幅长度为10%
	)

use xj_新冠疫苗接种管理系统
create table 接种人																	-- 创建接种人表
(
	idnum varchar(255) not null													-- 创建身份证号列
		constraint pk_idnum primary key clustered,						-- 身份证号作为主键
		check((idnum like '[1-9]'+replicate('[0-9]',16)+'[0-9x]' 		-- 检查是否符合身份证号格式约束
					and 
					isdate(substring(idnum,7,8))= 1)),
	name varchar(255) not null,													-- 创建姓名列
	gender nchar(1) not null,														-- 创建性别列
	age int not null,																	-- 创建年龄列
	is_vacced nchar(1) not null,													-- 创建是否接种列
)

use xj_新冠疫苗接种管理系统
create table 医生																		-- 创建医生表
(
	docid varchar(255) not null													-- 创建医生身份证号列
		constraint pk_docid primary key clustered,						-- 医生身份证号列作为主键
		check((docid like '[1-9]'+replicate('[0-9]',16)+'[0-9x]' 		-- 检查是否符合身份证号格式约束
					and 
					isdate(substring(docid,7,8))= 1)),
	name varchar(255) not null,													-- 创建姓名列
	hospital varchar(255) not null,												-- 创建所属医院列
)

use xj_新冠疫苗接种管理系统
create table 接种情况
(																						-- 创建接种情况表
	idnum varchar(255) not null primary key,						-- 创建身份证号列作为主键
	docid varchar(255) not null,											-- 创建接种医生列
	side_effect varchar(255) null,										-- 创建不良反应列
	place varchar(255) not null,											-- 创建接种地列
	vacdate date not null,													-- 创建接种日期列
	counts tinyint not null,													-- 创建接种次数列
	batch bigint not null,
	foreign key(idnum) references 接种人(idnum),				-- 创建接种情况和接种人的身份证外键关系
	foreign key(docid) references 医生(docid),					-- 创建接种情况和医生的身份证外键关系
	foreign key(batch) references 疫苗(batch)
)

use xj_新冠疫苗接种管理系统
create table 未接种情况													-- 创建未接种情况表
(
	idnum varchar(255) not null primary key,						-- 创建身份证号列作为主键
	will_vac nchar(1) not null,												-- 创建将来是否接种列
	reason varchar(255) not null,										-- 创建未接种原因列
	foreign key(idnum) references 接种人(idnum),				-- 创建未接种情况和接种人的身份证外键关系
)

use xj_新冠疫苗接种管理系统
create table 疫苗																-- 创建疫苗表
(																														    
	batch bigint not null primary key,									-- 创建批号列作为主键
	brand varchar(255) not null,											-- 创建品牌列
	expiry date not null														-- 创建有效期限
)


use xj_新冠疫苗接种管理系统		    -- 建立身份证号的唯一索引
create unique index u_idnum
on
接种人(idnum)

create unique index u_docid											    -- 建立医生身份证号的唯一索引
on
医生(docid)

create unique index u_batch												-- 建立批号的唯一索引
on 
疫苗(batch)

create unique nonclustered index not_vacced					--	建立是否接种的索引（接种人身份证号、姓名、是否接种）
on 接种人(idnum,name,is_vacced)

create nonclustered index what_reason						    -- 建立未接种原因的索引（未接种人身份证号 、未接种理由）
on 未接种情况(idnum,reason)

create nonclustered index what_side_effect					    -- 建立不良反应的索引（接种人身份证号、不良反应）
on 接种情况(idnum,side_effect)

-- 为接种人表插入值
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('41080119930228457X','向玉宇','男','28','是')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('510801197609022309','高新瑶','女','45','否')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('150401198107053872','孔弘济','男','40','否')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('130133197204039024','林冬卉','女','49','是')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('430102197606046442','邱迎海','女','45','是')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('632722197112040806','刘如松','女','50','否')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('130683199011300601','侯含雁','女','31','是')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('350111199409241690','赵弘新','男','27','是')
insert into xj_新冠疫苗接种管理系统.dbo.接种人
values ('1234567890','错误身份证','男','27','否')

-- 为医生表插入值
insert into xj_新冠疫苗接种管理系统.dbo.医生
values ('130133199002190972','孙卫国','衡阳市中心医院')
insert into xj_新冠疫苗接种管理系统.dbo.医生
values ('340203197406226174','宋曹根','衡阳市第一人民医院')
insert into xj_新冠疫苗接种管理系统.dbo.医生
values ('14042419861123283X','吴海港','衡阳市中心医院')
insert into xj_新冠疫苗接种管理系统.dbo.医生
values ('360923197402052549','郝丽芳','衡阳市第一人民医院')
insert into xj_新冠疫苗接种管理系统.dbo.医生
values ('371102197402178966','杨秀梅','衡阳市第一人民医院')

-- 为接种情况插入值
insert into xj_新冠疫苗接种管理系统.dbo.接种情况
values ('41080119930228457X','371102197402178966','发热','南华大学','20201103','2','1234567890')
insert into xj_新冠疫苗接种管理系统.dbo.接种情况
values ('130133197204039024','14042419861123283X','头晕','南华大学','20200503','2','1234567891')
insert into xj_新冠疫苗接种管理系统.dbo.接种情况
values ('430102197606046442','371102197402178966','头晕','衡阳市第一人民医院','20211203','2','1234567892')
insert into xj_新冠疫苗接种管理系统.dbo.接种情况
values ('130683199011300601','360923197402052549',null,'衡阳市第一人民医院','20200703','1','1234567893')
insert into xj_新冠疫苗接种管理系统.dbo.接种情况
values ('350111199409241690','371102197402178966','心跳加快','衡阳市中心医院','20200908','3','1234567894')
				
-- 为未接种情况表插入数据
insert into xj_新冠疫苗接种管理系统.dbo.未接种情况
values ('510801197609022309','否','过敏')
insert into xj_新冠疫苗接种管理系统.dbo.未接种情况
values ('150401198107053872','是','再观望一下')
insert into xj_新冠疫苗接种管理系统.dbo.未接种情况
values ('632722197112040806','否','过敏')

-- 为疫苗表插入数据
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567890','北京生物','20210202')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567891','科兴','20210405')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567892','科兴','20210608')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567893','北ro京生物','20210606')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567894','北京生物','20210708')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567895','科兴','20200101')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567896','科兴','20200202')
insert into xj_新冠疫苗接种管理系统.dbo.疫苗
values ('1234567897','北京生物','20200102')


-- 创建各表查询
use xj_新冠疫苗接种管理系统
select *
from 接种情况
use xj_新冠疫苗接种管理系统
select *
from 接种人
use xj_新冠疫苗接种管理系统
select *
from 未接种情况
use xj_新冠疫苗接种管理系统
select *
from 疫苗
use xj_新冠疫苗接种管理系统
select *
from 医生


-- 创建医生姓名以及所属医院的存储过程
use xj_新冠疫苗接种管理系统
go
create procedure docinfo
as
begin
	select name, hospital
	from xj_新冠疫苗接种管理系统.dbo.医生
end
go
exec docinfo


-- 创建计算已接种人数的存储过程
use xj_新冠疫苗接种管理系统
go
create procedure how_many_vacced
as
begin
	declare @counts int;														    -- 定义int型变量
	set @counts=(select count(a.is_vacced) from(select  * from 接种人 where is_vacced='是') a) 	 
																							-- 查询是否接种栏目为是的列作为新表a，并使用count函数计算列数
	print '已经有'+cast(@counts as varchar)+'人接种了疫苗'  -- 输出结果使用cast函数将int型变量转换为varchar型变量
end
go
exec how_many_vacced
	

-- 创建某医生接种人数的函数
use xj_新冠疫苗接种管理系统
go
create function docscore(@docid varchar(255))
returns	table
as
return 
(
	select idnum from 接种情况 where docid=@docid	
)
select * from xj_新冠疫苗接种管理系统.dbo.docscore('371102197402178966')


-- 创建已经过期的疫苗视图
use xj_新冠疫苗接种管理系统
go
create view outdated
as
select * from 疫苗 where expiry <= convert(date, getdate())	   
						-- 选择所有有效期限小于今天（使用getdate函数获取）的疫苗信息
go
select * from outdated


-- 创建性别为女性并且有不良反应的接种人信息视图
use xj_新冠疫苗接种管理系统
go
create view side_effect_female
as
select name, a.idnum, side_effect
from 接种人 as a
join 接种情况 as b on b.idnum=a.idnum				 --	 首先建立接种人和接种情况表之间的连接
where a.gender='女' and side_effect is not null	 -- 然后选择女性并且有不良反应的列
go
select * from side_effect_female